import concurrent.futures
import json
import os
from datetime import datetime

import pandas as pd
import requests

import mysql.connector

# 连接到 MySQL 数据库
connection = mysql.connector.connect(
    host="127.0.0.1",  # 替换为实际的主机名
    user="root",  # 替换为实际的用户名
    password="520777",  # 替换为实际的密码
    database="house"  # 替换为实际的数据库名
)
# 创建游标对象
cursor = connection.cursor()
# 获取当前 Python 文件的路径
current_dir = os.path.dirname(os.path.abspath(__file__))
# 指定存放 JSON 文件的文件夹路径
# folder_path = os.path.join(current_dir, 'resource', 'detail_json_20240203')
folder_path = 'D:\\Project\\Python\\fang\\resource\\detail_json'
# 读取文件夹中的文件名
file_names = os.listdir(folder_path)
# 对文件名进行排序
sorted_file_names = sorted(file_names)
# house 表字段集和
columns = ['house_id', 'section', 'unit_no', 'block_no', 'cell', 'price', 'unit_price', 'area', 'room', 'hall', 'toilet', 'floor', 'total_floor', 'decoration', 'create_time', 'sell_point', 'struct', 'in_area', 'house_sub_type', 'remark',
        'build_year', 'hu', 'balcony', 'open_type', 'fitment', 'property_date', 'owner_name', 'property_type', 'paymode', 'situation', 'buy_price', 'period', 'property_years', 'level', 'loaning', 'property_cert', 'towards', 'info_from', 'private_remark', 'have_elevator', 'ti', 'uniq', 'min_price']

def getSqlStr(type):
    """拼装sql

    Args:
        type (string): 类型

    Returns:
        string: 拼装的sql
    """
    if type == 'columns':
        return "`" + "`,`".join(columns) + "`"
    
    if type == 'datas':
        # return ', '.join([f"house['{col.replace('_', '').capitalize()}']" for col in columns])
        # return ', '.join([f"house['{col.split('_')[0].capitalize() + ''.join(word.capitalize() for word in col.split('_')[1:])}']" for col in columns])
        # return ', '.join([f"house['{col.split('_')[0].lower() + ''.join(word.capitalize() for word in col.split('_')[1:])}']" for col in columns])
        return [f"house['{col.split('_')[0].lower() + ''.join(word.capitalize() for word in col.split('_')[1:])}']" for col in columns]

    if type == 'values':
        return ",".join(["%s" for _ in columns])

def count(house_id):
    """查询数量

    Args:
        house_id (String): 房源id

    Returns:
        int: 数量
    """
    
    sql = f'select count(1) from house.house where house_id = {house_id}'
    
    # 执行 SQL 查询
    cursor.execute(sql)

    # 获取查询结果
    result = cursor.fetchone()

    return result[0]  

# 循环读取文件夹中的 JSON 文件
for filename in sorted_file_names:
    if filename.endswith('.json'):
        try:
            file_path = os.path.join(folder_path, filename)

            # 读取文件中的 JSON 数据，指定编码方式为 'utf-8'
            with open(file_path, 'r', encoding='utf-8') as file:
                json_data = json.load(file)

            # 房产信息
            house_id = json_data['house']['houseId']
            house = json_data['house']
            
            # 将时间戳转换为 datetime 对象
            timestamp = datetime.fromtimestamp(int(house['createTime']) / 1000.0)
            # 格式化为 MySQL datetime 格式
            formatted_datetime = timestamp.strftime('%Y-%m-%d %H:%M:%S')
            
            # 更新数据
            if count(house_id) > 0:

                # 构建插入数据的 SQL 查询
                sql = "UPDATE house.house set "
                for col in columns:
                    new_col = col.split('_')[0].lower() + ''.join(word.capitalize() for word in col.split('_')[1:])
                    val = house[f'{new_col}']
                    if new_col == 'createTime':
                        val = formatted_datetime
                    sql += f"{col} = '{val}'"
                    sql += ', '
                parts = sql.split(',')
                sql = ', '.join(parts[:-1])
                sql += f'where house_id = {house_id}'

                # 执行 SQL 查询
                cursor.execute(sql)
                connection.commit()
                print(f"更新{house_id}成功")
            # 新增数据
            else:
                # 构建插入数据的 SQL 查询
                sql = f"INSERT INTO house ({getSqlStr('columns')}) VALUES ({getSqlStr('values')})"
            
                values = []
                for col in columns:
                    new_col = col.split('_')[0].lower() + ''.join(word.capitalize() for word in col.split('_')[1:])
                    
                    # 日期
                    if new_col == 'createTime':
                        values.append(formatted_datetime)
                        continue
                    
                    values.append(house[f'{new_col}'])
                    
                # 执行 SQL 查询
                cursor.execute(sql, tuple(values))
                connection.commit()
                print(f"写入{house_id}成功")

        except Exception as e:
            print("发生异常:", str(e))


# 关闭游标和数据库连接
cursor.close()
connection.close()

# 打印输出文件路径
print(f'Saved data to Excel file: {len(sorted_file_names)}')
